package dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import model.PlanningBoard;
import model.Subject;

public class PlanningBoardMod {
	Connection connection;
	public static PlanningBoardMod isntancePBM;
	public static PlanningBoardMod getIsntance(){
		if( isntancePBM == null){
			isntancePBM = new PlanningBoardMod();
		}
		return isntancePBM;
	}
	public boolean addPB(PlanningBoard p){
		boolean result = false;
		PreparedStatement sqli = null;
		try {
			connection = DB_Connection.getIsntance().getConnection();
			String sql = "INSERT INTO planning_board (clid, uid, sjid , scholastic , half ) " +
					"VALUES (? , ?  , ?  , ?  , ? )";
			sqli = connection.prepareStatement(sql);
			sqli.setInt(1, p.getClid());
			sqli.setInt(2, p.getUid());
			sqli.setInt(3, p.getSjid());
			sqli.setString(4, p.getScholastic());
			sqli.setInt(5, p.getHalf());
			int rs = sqli.executeUpdate();
			if(rs > 0)result = true;
			sqli.close();
			connection.close();
		} catch (Exception e) {
			// TODO: handle exception
		}
		return result;
	}
	public boolean UpDateByPbid(PlanningBoard pb ,int id) throws SQLException{
		boolean result =false;
		PreparedStatement sqlu = null;
		try {
			connection = DB_Connection.getIsntance().getConnection();
			String sql ="UPDATE planning_board SET clid = ? , uid = ? , sjid = ?, scholastic = ? , half  = ? WHERE pbid = ? LIMIT = 1 ";
			sqlu = connection.prepareStatement(sql);
			sqlu.setInt(1, pb.getClid());
			sqlu.setInt(2, pb.getUid());
			sqlu.setInt(3, pb.getSjid());
			sqlu.setString(4, pb.getScholastic());
			sqlu.setInt(6, pb.getHalf());
			sqlu.setInt(7, id);
			int number = sqlu.executeUpdate();
			if(number>0) result = true;
			sqlu.close();
			connection.close();
		} catch (Exception e) {
			// TODO: handle exception
		}
		return result;
	}
	public ArrayList<PlanningBoard> getAll(){
		ArrayList<PlanningBoard> result = null;
		PreparedStatement sqls = null;
		try {
			connection = DB_Connection.getIsntance().getConnection();
			String sql ="SELECT pbid, clid, uid, sjid, scholastic, half FROM planning_board";
			sqls = connection.prepareStatement(sql);
			ResultSet rs = sqls.executeQuery();
			while(rs.next()){
				result.add(new PlanningBoard(rs.getInt(1), rs.getInt(2), rs.getInt(3), rs.getInt(4), rs.getString(5), rs.getInt(5)));
			}
			sqls.close();
			connection.close();
		} catch (Exception e) {
			// TODO: handle exception
		}
		return result;
	}
	public ArrayList<PlanningBoard> getByClid( int cl){
		ArrayList<PlanningBoard> result = null;
		PreparedStatement sqls = null;
		try {
			connection = DB_Connection.getIsntance().getConnection();
			String sql ="SELECT pbid, clid, uid, sjid, scholastic, half FROM planning_board WHERE clid = ?";
			sqls = connection.prepareStatement(sql);
			sqls.setInt(1, cl);
			ResultSet rs = sqls.executeQuery();
			while(rs.next()){
				result.add(new PlanningBoard(rs.getInt(1), rs.getInt(2), rs.getInt(3), rs.getInt(4), rs.getString(5), rs.getInt(5)));
			}
			sqls.close();
			connection.close();
		} catch (Exception e) {
			// TODO: handle exception
		}
		return result;
	}
	public ArrayList<PlanningBoard> getByUid( int uid){
		ArrayList<PlanningBoard> result = null;
		PreparedStatement sqls = null;
		try {
			connection = DB_Connection.getIsntance().getConnection();
			String sql ="SELECT pbid, clid, uid, sjid, scholastic, half FROM planning_board WHERE uid = ?";
			sqls = connection.prepareStatement(sql);
			sqls.setInt(1, uid);
			ResultSet rs = sqls.executeQuery();
			while(rs.next()){
				result.add(new PlanningBoard(rs.getInt(1), rs.getInt(2), rs.getInt(3), rs.getInt(4), rs.getString(5), rs.getInt(5)));
			}
			sqls.close();
			connection.close();
		} catch (Exception e) {
			// TODO: handle exception
		}
		return result;
	}
	public ArrayList<PlanningBoard> getByScholastic(String scholastic){
		ArrayList<PlanningBoard> result = null;
		PreparedStatement sqls = null;
		try {
			connection = DB_Connection.getIsntance().getConnection();
			String sql ="SELECT pbid, clid, uid, sjid, scholastic, half FROM planning_board WHERE scholastic = ?";
			sqls = connection.prepareStatement(sql);
			sqls.setString(1, scholastic);
			ResultSet rs = sqls.executeQuery();
			while(rs.next()){
				result.add(new PlanningBoard(rs.getInt(1), rs.getInt(2), rs.getInt(3), rs.getInt(4), rs.getString(5), rs.getInt(5)));
			}
			sqls.close();
			connection.close();
		} catch (Exception e) {
			// TODO: handle exception
		}
		return result;
	}
	public ArrayList<PlanningBoard> getByHalf(String scholastic, int half){
		ArrayList<PlanningBoard> result = null;
		PreparedStatement sqls = null;
		try {
			connection = DB_Connection.getIsntance().getConnection();
			String sql ="SELECT pbid, clid, uid, sjid, scholastic, half FROM planning_board WHERE scholastic = ? AND half = ?";
			sqls = connection.prepareStatement(sql);
			sqls.setString(1, scholastic);
			sqls.setInt(2, half);
			ResultSet rs = sqls.executeQuery();
			while(rs.next()){
				result.add(new PlanningBoard(rs.getInt(1), rs.getInt(2), rs.getInt(3), rs.getInt(4), rs.getString(5), rs.getInt(5)));
			}
			sqls.close();
			connection.close();
		} catch (Exception e) {
			// TODO: handle exception
		}
		return result;
	}
	public boolean delByPbid(int pbid){
		PreparedStatement delpd = null;
		connection = DB_Connection.getIsntance().getConnection();
		boolean result = false;
		try {
			
			delpd = connection.prepareStatement("DELETE FROM planning_board WHERE pbid = ? LIMIT 1");
			delpd.setInt(1, pbid);
			int number = delpd.executeUpdate();
			if (number > 0) {
				result = true;
			}
			delpd.close();
			connection.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return  result;
	}
}
